#!/bin/bash
# 功能：本地目录数据上传到hive,并进行数清洗，join
# 总结
# 1. shell 多命令依赖顺序执行：多条命令执行，前面命令执行成功才执行后面命令 (command1) && (command2)
# 2. hive beeline 执行多条SQL（顺序执行）:-e "sql1" "sql2"
#
source ~/.bash_profile
( `/opt/hadoop-3.1.1.3.1.4.0-315/bin/hadoop fs -mkdir -p /user/quantum_social/dw/bia_gdb/bayou/ec/taobao/data_bayou_ec_taobao_comment_ods/dt=$1 ` ) &&
(  `/opt/hadoop-3.1.1.3.1.4.0-315/bin/hadoop fs -put /data/bayou/ec/fit/comment/$1/* /user/quantum_social/dw/bia_gdb/bayou/ec/taobao/data_bayou_ec_taobao_comment_ods/dt=$1 `) &&
(  old_beeline -u "jdbc:hive2://nb-hive.mininglamp.com:10000/bia_gdb?mapreduce.job.queuename=root.marvel.dms" -n "bia_data" -p "]v@39)3$%2|*slQ=" -e \
"set hive.merge.tezfiles=true;" \
 "alter table bia_gdb.data_bayou_ec_taobao_comment_ods add if not exists partition(dt='$1') location '/user/quantum_social/dw/bia_gdb/bayou/ec/taobao/data_bayou_ec_taobao_comment_ods/dt=$1' ;"\
 "/*dwd数据修复*/INSERT Into TABLE bia_gdb.data_bayou_ec_taobao_comment_dwd partition(dt = '$1') SELECT  id ,  kw_source ,  kw_supplier ,  kw_cid ,  kw_url ,  date_publishedAt , get_json_object(text_content, '$.comment') as text_content, imagesToList(kw_images) as kw_images,  long_viewCount ,  long_likeCount ,  long_commentCount ,  kw_uid ,  kw_nickName ,  text_nickName ,  long_score ,  kw_userLevel ,  kw_profileImageUrl ,  text_appendedContent ,  kw_productID ,  ts ,  reply ,  append_reply ,  skuMap ,  skuId , jsontobj(skumap, skuid) as  object_prooductBranchPrpos , aux FROM bia_gdb.data_bayou_ec_taobao_comment_ods where dt = '$1' ; " \
 "/*dws数据修复*/ INSERT into TABLE bia_gdb.data_bayou_ec_tmall_comment_dws partition(dt = '$2') select comment.id, comment.kw_productid, comment.kw_source, comment.kw_supplier, comment.kw_cid, comment.kw_url, comment.date_publishedAt, comment.text_content, comment.kw_images, comment.long_viewCount, comment.long_likeCount, comment.long_commentCount, comment.kw_uid, comment.kw_nickName, comment.text_nickName, comment.long_score, comment.kw_userLevel, comment.kw_profileImageUrl, comment.text_appendedContent, comment.text_reply, comment.text_appendReply, comment.skuMap, comment.skuId, comment.object_prooductBranchPrpos, commody.kw_productName, commody.text_productName, commody.bool_productShelves, commody.kw_productUrl, commody.double_productPrice, commody.long_productCommentCount, commody.kw_productCategoryID, commody.kw_productCategoryName, commody.text_productCategoryName, commody.kw_productBrandID, commody.kw_productBrandName, commody.text_productBrandName, commody.long_productMonthSoldCount, commody.long_productTotalSoldCount, commody.kw_retailPlatform, commody.kw_storeID, commody.kw_storeName, commody.text_storeName, commody.kw_storeUrl, commody_info.images as kw_productImages, commody_info.props, commody_info.nested_productProps, commody_info.props2, comment.aux from ( select comdwd.id, comdwd.kw_productid, comdwd.kw_source, comdwd.kw_supplier, comdwd.kw_cid, comdwd.kw_url, comdwd.date_publishedAt, comdwd.text_content, comdwd.kw_images, comdwd.long_viewCount, comdwd.long_likeCount, comdwd.long_commentCount, comdwd.kw_uid, comdwd.kw_nickName, comdwd.text_nickName, comdwd.long_score, comdwd.kw_userLevel, comdwd.kw_profileImageUrl, comdwd.text_appendedContent, comdwd.reply as text_reply, comdwd.append_reply as text_appendReply, comdwd.skuMap, comdwd.skuId, comdwd.object_prooductBranchPrpos, comdwd.aux from bia_gdb.data_bayou_ec_taobao_comment_dwd comdwd where ( comdwd.dt = '$1' ) ) comment left join ( select t.* from ( select cmd.*, row_number() over ( partition by cmd.kw_productID order by cmd.ts desc ) as new_index from bia_gdb.data_bayou_ec_taobao_commody_dwd cmd where cmd.dt >= "20221115" ) t where t.new_index = 1 ) commody on comment.kw_productID = commody.kw_productID left join ( select t.* from ( select cmdi.*, row_number() over ( partition by cmdi.kw_productID order by cmdi.ts desc ) as new_index from bia_gdb.data_bayou_ec_taobao_commody_info_dwd cmdi ) t where t.new_index = 1 ) commody_info on comment.kw_productID = commody_info.kw_productID " )